package cn.kgc.jdbc.dao;

import cn.kgc.jdbc.entity.Score;
import cn.kgc.jdbc.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/4/22
 */
public class ScoreDaoImpl implements  ScoreDao {

    @Override
    public int addScore(long stuId, long courseId, double score) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into score(stu_id,course_id,score)values(?,?,?)";
        try {
            int update = queryRunner.update(sql, stuId, courseId, score);
            return  update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Score> selectAll(String courseName) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "SELECT  stu_id AS stuId ,course_id AS courseId,score, " +
                "stu_name AS stuName ,course_name AS courseName  FROM score " +
                " join student ON student.id  = score.stu_id " +
                " JOIN course ON   score.course_id = course.id  where course_name = ?  order by score  desc" ;

        try {
            List<Score> scores = queryRunner.query(sql, new BeanListHandler<>(Score.class), courseName);
            return scores;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
